﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using BCNPCObject.DB;
using DbLinq;
using DbLinq.Oracle;
using CMISLibrary.Entities;
using CMISLibrary;

namespace BCNPCObject.EXCEL
{
    public class cls_CN_BB_GTHU_Controller
    {
         #region   Atributes

        //private DataSet CMIS_Header = new CMISOutputParameter();
        private CN_BB_GTHU info;

        #endregion

        #region   Constructor

        public cls_CN_BB_GTHU_Controller()
        {
            info = new CN_BB_GTHU();
        }
        public cls_CN_BB_GTHU_Controller(CN_BB_GTHU Info)
        {
            this.info = Info;
        }

        #endregion

        #region   Properties

        public CN_BB_GTHU pInfor
        {
            get { return info; }
            set { info = value; }
        }
        #endregion
        #region Methods
        public DataSet select_BB_GIAO_THU(string ma_dviqly, string ma_thungan)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                var result = from p in CMIS2.DB.CN_BB_GTHU.ToList()
                             where (p.MA_DVIQLY == ma_dviqly && p.MA_TNGAN == ma_thungan && p.SO_BBQTOAN == 0)
                             join nv in CMIS2.DB.D_NHAN_VIEN.ToList() on new { p.MA_DVIQLY, p.MA_NVIEN }
                                                                    equals new { nv.MA_DVIQLY, nv.MA_NVIEN } into nv0
                             from nv in nv0.DefaultIfEmpty()
                             orderby p.SO_BBGIAO descending
                             select new
                             {
                                 CHON = "false",
                                 p.SO_BBGIAO,
                                 p.MA_DVIQLY,
                                 p.MA_TNGAN,
                                 NGAY_GIAO = p.NGAY_GIAO.ToString("dd/MM/yyyy"),
                                 nv.TEN_NVIEN,
                                 TSO_TIEN = p.TSO_TIEN.ToString("#,###,###,###"),
                                 TSO_GTGT = p.TSO_GTGT.ToString("#,###,###,###"),
                                 p.TSO_HDON,
                                 TONGTIEN = (p.TSO_TIEN + p.TSO_GTGT).ToString("#,###,###,###"),
                             };
                    dt = Utility.LINQToDataTable(result);
                    ds.Tables.Add(dt);
                    return ds;
            }
            catch (Exception ex)
            {
                //maloi = ex.ToString();
                CMIS2.ResetDB(); return null;
            }
        }
        public DataSet select_HOADON_GIAO_THU(string ma_dviqly, long BBAN_GIAO)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                var a = (from p in CMIS2.DB.CN_HDON_GTHU
                             where p.MA_DVIQLY == ma_dviqly && p.SO_BBGIAO == BBAN_GIAO && p.SO_BBQTOAN == 0
                             select p).ToList();
                var result = from b in a
                             select new
                             {
                                 ID_HDON = b.ID_HDON,
                                 SO_TIEN = b.SO_TIEN.ToString("#,###,###,###"),
                                 TIEN_GTGT = b.TIEN_GTGT.ToString("#,###,###,###"),
                                 TONG_TIEN = (b.SO_TIEN + b.TIEN_GTGT).ToString("#,###,###,###"),
                                 MA_SOGCS = b.MA_SOGCS
                             };
                dt = Utility.LINQToDataTable(result);
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                //maloi = ex.ToString();
                CMIS2.ResetDB(); return null;
            }
        }
        public DataSet select_CN_BB_GTHU(string ma_dviqly,string ma_TNV,DataSet dsbb)
        {
            DataTable dt = new DataTable();
            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();
            DataTable dt3 = new DataTable();
            DataSet ds = new DataSet();
            int thang = 0, nam = 0;
            List<Int64> intList = new List<Int64>();
            for (int i = 0; i < dsbb.Tables[0].Rows.Count; i++)
            {
                if (dsbb.Tables[0].Rows[i]["CHON"].ToString() == "True")
                {
                    intList.Add(Convert.ToInt64(dsbb.Tables[0].Rows[i]["SO_BBGIAO"]));
                }
            }
            try
            {
                // LẤY THÁNG NĂM HÓA ĐƠN

                var thangnam = (from t in CMIS2.DB.S_THANG_LVIEC
                                where t.MA_DVIQLY == ma_dviqly && t.PHAN_HE == "CN"
                               select t).ToList();
                if (thangnam.Count != 0)
                {
                    thang = thangnam[0].THANG;
                    nam = thangnam[0].NAM;
                }
                //List<CN_THEODOINO> CONG_NO = new List<CN_THEODOINO>();
                List<CN_THEODOINO> CONG_NO = (from n in CMIS2.DB.CN_THEODOINO
                                              where n.MA_DVIQLY == ma_dviqly && (n.TIEN_NO != 0 || n.THUE_NO != 0) && n.TTRANG_SSAI != 1     
                                                   select n).ToList();
                var result = from a in CMIS2.DB.CN_BB_GTHU.ToList()
                             where a.MA_DVIQLY == ma_dviqly && a.MA_TNGAN == ma_TNV && intList.Contains(a.SO_BBGIAO) && a.SO_BBQTOAN == 0
                             select new {
                                 a.MA_DVIQLY,
                                 a.SO_BBGIAO,
                                 a.SO_BBQTOAN,
                                 a.LOAI_BBAN,
                                 a.MA_TNGAN,
                                 a.MA_NVIEN,
                                 a.NGAY_GIAO,
                                 //a.NGAY_QTOAN,
                                 a.TSO_TIEN,
                                 a.TSO_GTGT,
                                 a.TSO_HDON,
                                 a.TSO_TIENQT,
                                 a.TSO_GTGTQT,
                                 a.TSO_HDONQT,
                                 a.NGAY_TAO,
                                 a.NGUOI_TAO,
                                 a.NGAY_SUA,
                                 a.NGUOI_SUA,
                                 a.MA_CNANG,
                                 THANG_HDON = thang,
                                 NAM_HDON = nam,
                             };
                dt = Utility.LINQToDataTable(result);
                dt.TableName = "CN_BB_GTHU";
                ds.Tables.Add(dt);
                
                var result1 = from a1 in CMIS2.DB.CN_HDON_GTHU.ToList()
                              join g in result on new { a1.MA_DVIQLY, a1.SO_BBGIAO }
                                                     equals new { g.MA_DVIQLY, g.SO_BBGIAO }
                              join g1 in CONG_NO on new { a1.MA_DVIQLY, a1.ID_HDON }
                                                     equals new { g1.MA_DVIQLY, g1.ID_HDON }
                              where a1.MA_DVIQLY == ma_dviqly && a1.SO_BBQTOAN == 0
                              select new
                              {
                                  a1.MA_DVIQLY,
                                  a1.ID_HDON,
                                  a1.LOAI_HDON,
                                  a1.SO_BBGIAO,
                                  a1.SO_BBQTOAN,
                                  a1.SO_TIEN,
                                  a1.TIEN_GTGT,
                                  a1.SO_TIENQT,
                                  a1.TIEN_GTGTQT,
                                  a1.MA_SOGCS,
                                  a1.NGAY_TAO,
                                  a1.NGUOI_TAO,
                                  a1.NGAY_SUA,
                                  a1.NGUOI_SUA,
                                  a1.MA_CNANG,
                                  THANG_HDON = thang,
                                  NAM_HDON = nam,
                              };
                dt1 = Utility.LINQToDataTable(result1);
                dt1.TableName = "CN_HDON_GTHU";
                ds.Tables.Add(dt1);
                var result2 = from a1 in CONG_NO.ToList()
                              join g in result1 on new { a1.MA_DVIQLY, a1.ID_HDON }
                                                     equals new { g.MA_DVIQLY, g.ID_HDON }
                              where a1.MA_DVIQLY == ma_dviqly
                              select new
                              {
                                  a1.MA_DVIQLY,
                                  a1.ID_HDON,
                                  a1.LOAI_HDON,
                                  a1.LOAI_PSINH,
                                  a1.MA_KHANG,
                                  a1.MA_KHTT,
                                  a1.TEN_KHANG,
                                  a1.TEN_KHTT,
                                  a1.DCHI_KHANG,
                                  a1.DCHI_KHTT,
                                  a1.LOAI_KHANG,
                                  a1.MANHOM_KHANG,
                                  a1.NAM,
                                  a1.THANG,
                                  a1.KY,
                                  a1.TIEN_PSINH,
                                  a1.THUE_PSINH,
                                  a1.TIEN_NO,
                                  a1.THUE_NO,
                                  a1.MA_PTTT,
                                  MA_TO = a1.MA_TO == null ? " " : a1.MA_TO,
                                  a1.MA_SOGCS,
                                  MA_KVUC = a1.MA_KVUC == null ? " " : a1.MA_KVUC,
                                  a1.STT_TRANG,
                                  a1.STT,
                                  a1.TTHAI_NO_HTAI,
                                  a1.TTRANG_SSAI,
                                  a1.TTHAI_GIAO,
                                  a1.LAN_GIAO,
                                  a1.MA_DVU_TTOAN,
                                  a1.HTHUC_TTOAN,
                                  a1.LOAI_TBAO,
                                  a1.CHUYEN_DVQLY,
                                  a1.NGAY_PHANH,
                                  a1.NGAY_TAO,
                                  a1.NGUOI_TAO,
                                  a1.NGAY_SUA,
                                  a1.NGUOI_SUA,
                                  a1.MA_CNANG,
                                  THANG_HDON = thang,
                                  NAM_HDON = nam,
                                  SO_SERY = a1.SO_SERY == null ? " " : a1.SO_SERY,
                              };
                dt2 = Utility.LINQToDataTable(result2);
                dt2.TableName = "CN_THEODOINO";
                ds.Tables.Add(dt2);
                var result3 = from a1 in CMIS2.DB.CN_TTHUA_KHANG.ToList()
                              where a1.MA_DVIQLY == ma_dviqly
                              select new
                              {
                                  a1.MA_DVIQLY,
                                  a1.MA_KHTT,
                                  a1.TIEN_THUALK,
                                  a1.TTHAI_NO_HTAI,
                                  a1.CHUYEN_DVQLY,
                                  a1.NGAY_TAO,
                                  a1.NGUOI_TAO,
                                  a1.NGAY_SUA,
                                  a1.NGUOI_SUA,
                                  a1.MA_CNANG,
                                  //a1.TIEN_THUAPS
                              };
                dt3 = Utility.LINQToDataTable(result3);
                dt3.TableName = "CN_TTHUA_KHANG";
                ds.Tables.Add(dt3);
                return ds;
            }
            catch (Exception ex)
            {
                //maloi = ex.ToString();
                CMIS2.ResetDB(); return null;
            }
        }

        public void select_THANGNAMCONGNO(string ma_dviqly,ref int thang, ref int nam)
        {
            try
            {
                var thangnam = (from t in CMIS2.DB.S_THANG_LVIEC
                                where t.MA_DVIQLY == ma_dviqly && t.PHAN_HE == "CN"
                                select t).ToList();
                if (thangnam.Count != 0)
                {
                    thang = thangnam[0].THANG;
                    nam = thangnam[0].NAM;
                }
            }
            catch (Exception ex)
            {
                //maloi = ex.ToString();
                thang = 0;
                nam = 0;
                CMIS2.ResetDB();
            }
        }
        public DataSet select_MATRAM(string ma_dviqly,string ma_khang)
        {
            DataTable dt = new DataTable();
            DataSet ds = new DataSet();
            try
            {
                var _ngay = (from a in CMIS2.DB.HDG_VITRI_DDO.ToList()
                             where a.MA_DDO.Substring(0, 13) == ma_khang && a.MA_DVIQLY == ma_dviqly
                                 select a);
                DateTime _ngay_MAX = (from q2 in _ngay select q2).AsEnumerable().Max(m => Convert.ToDateTime(m.NGAY_HLUC));
                if (_ngay_MAX == null)
                {
                    CMIS2.ResetDB();
                    return null;
                }
                var result = (from p in CMIS2.DB.HDG_VITRI_DDO.ToList()
                              where p.MA_DDO.Substring(0, 13) == ma_khang && p.MA_DVIQLY == ma_dviqly && p.NGAY_HLUC == _ngay_MAX
                        orderby p.MA_DDO
                        select new
                        {
                            p.MA_DDO,
                            p.MA_TRAM,
                            p.SO_HOM,
                        }).ToList();
                dt = Utility.LINQToDataTable(result);
                ds.Tables.Add(dt);
                return ds;
            }
            catch (Exception ex)
            {
                //maloi = ex.ToString();
                return null;
                CMIS2.ResetDB();
            }
        }
        #endregion
    }
}
